using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class CategoriesDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procCategories_add]";
        private string _strSPUpdateName = "dbo.[procCategories_update]";
        private string _strSPDeleteName = "dbo.[procCategories_delete]";
        private string _strSPGetName = "dbo.[procCategories_get]";
        private string _strSPGetAllName = "dbo.[procCategories_getall]";
        private string _strSPGetPages = "dbo.[procCategories_getpaged]";
        private string _strSPIsExist = "dbo.[procCategories_isexist]";
        private string _strSPRoots = "dbo.[procCategories_getRoots]";
        private string _strSPRootsType = "dbo.[procCategories_getRootsType]";
        private string _strSPGetChilds = "dbo.[procCategories_getChilds]";
        private string _strTableName = "[Categories]";
        private string _strSPLeafs = "dbo.[sp_Categories_getLeafs]";
        private string _strSPGetTransferOutName = "dbo.[procCategories_gettransferout]";
        private string _strSPGetAllChilds = "procCategories_getAllChilds";
        string _strSPGetAllParents = "procCategories_getAllParents";
        string _strSPCateAllByPro = "procCategories_getallpro";
        private string _strSPGetIDByPro = "procCategories_getpro";
        #endregion Local Variable

        #region Method
        public CategoriesInfo Get(
        Int32 ID,
        ref string sErr)
        {
            CategoriesInfo objEntr = new CategoriesInfo();
            connect();
            InitSPCommand(_strSPGetName);
            AddParameter(CategoriesInfo.Field.ID.ToString(), ID);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            if (list.Rows.Count > 0)
                objEntr = (CategoriesInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            CategoriesInfo result = new CategoriesInfo();
            result.ID = (dt.Rows[i][CategoriesInfo.Field.ID.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][CategoriesInfo.Field.ID.ToString()]));
            result.Name = (dt.Rows[i][CategoriesInfo.Field.Name.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][CategoriesInfo.Field.Name.ToString()]));
            result.NameVN = (dt.Rows[i][CategoriesInfo.Field.NameVN.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][CategoriesInfo.Field.NameVN.ToString()]));
            result.Rank = (dt.Rows[i][CategoriesInfo.Field.Rank.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][CategoriesInfo.Field.Rank.ToString()]));
            result.LinkedID = (dt.Rows[i][CategoriesInfo.Field.LinkedID.ToString()] == DBNull.Value ? -1 : Convert.ToInt32(dt.Rows[i][CategoriesInfo.Field.LinkedID.ToString()]));
            result.Level = (dt.Rows[i][CategoriesInfo.Field.Level.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][CategoriesInfo.Field.Level.ToString()]));
            result.Status = (dt.Rows[i][CategoriesInfo.Field.Status.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][CategoriesInfo.Field.Status.ToString()]));
            result.Image = (dt.Rows[i][CategoriesInfo.Field.Image.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][CategoriesInfo.Field.Image.ToString()]));
            result.Decribes = (dt.Rows[i][CategoriesInfo.Field.Decribes.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][CategoriesInfo.Field.Decribes.ToString()]));
            result.Type = (dt.Rows[i][CategoriesInfo.Field.Type.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][CategoriesInfo.Field.Type.ToString()]));

            return result;
        }

        public DataTable GetAll(
        ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllName);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetByPos(
        int pos, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllName);
            AddParameter("INX", pos);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public int GetCountRecord(
        ref string sErr)
        {
            int ret = -1;
            connect();
            InitSPCommand(_strSPInsertName);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(CategoriesInfo objEntr, ref string sErr)
        {
            int ret = -1;
            connect();
            InitSPCommand(_strSPInsertName);
            AddParameter(CategoriesInfo.Field.Name.ToString(), objEntr.Name);
            AddParameter(CategoriesInfo.Field.NameVN.ToString(), objEntr.NameVN);
            AddParameter(CategoriesInfo.Field.Rank.ToString(), objEntr.Rank);
            object temp = DBNull.Value;
            if (objEntr.LinkedID != -1) temp = objEntr.LinkedID;
            AddParameter(CategoriesInfo.Field.LinkedID.ToString(), temp);
            AddParameter(CategoriesInfo.Field.Level.ToString(), objEntr.Level);
            AddParameter(CategoriesInfo.Field.Status.ToString(), objEntr.Status);
            AddParameter(CategoriesInfo.Field.Image.ToString(), objEntr.Image);
            AddParameter(CategoriesInfo.Field.Decribes.ToString(), objEntr.Decribes);
            AddParameter(CategoriesInfo.Field.Type.ToString(), objEntr.Type);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(CategoriesInfo objEntr)
        {
            connect();
            InitSPCommand(_strSPUpdateName);
            AddParameter(CategoriesInfo.Field.ID.ToString(), objEntr.ID);
            AddParameter(CategoriesInfo.Field.Name.ToString(), objEntr.Name);
            AddParameter(CategoriesInfo.Field.NameVN.ToString(), objEntr.NameVN);
            AddParameter(CategoriesInfo.Field.Rank.ToString(), objEntr.Rank);
            object temp = DBNull.Value;
            if (objEntr.LinkedID != -1) temp = objEntr.LinkedID;
            AddParameter(CategoriesInfo.Field.LinkedID.ToString(), temp);

            AddParameter(CategoriesInfo.Field.Level.ToString(), objEntr.Level);
            AddParameter(CategoriesInfo.Field.Status.ToString(), objEntr.Status);
            AddParameter(CategoriesInfo.Field.Image.ToString(), objEntr.Image);
            AddParameter(CategoriesInfo.Field.Decribes.ToString(), objEntr.Decribes);
            AddParameter(CategoriesInfo.Field.Type.ToString(), objEntr.Type);

            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(
        Int32 ID
        )
        {
            connect();
            InitSPCommand(_strSPDeleteName);
            AddParameter(CategoriesInfo.Field.ID.ToString(), ID);

            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public DataTableCollection Get_Page(CategoriesInfo obj, string orderBy, int pageIndex, int pageSize, ref String sErr)
        {
            string whereClause = CreateWhereClause(obj);
            DataTableCollection dtList = null;
            connect();
            InitSPCommand(_strSPGetPages);

            AddParameter("WhereClause", whereClause);
            AddParameter("OrderBy", orderBy);
            AddParameter("PageIndex", pageIndex);
            AddParameter("PageSize", pageSize);

            try
            {
                dtList = executeCollectSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return dtList;
        }

        public Boolean IsExist(
        Int32 ID
        )
        {
            connect();
            InitSPCommand(_strSPIsExist);
            AddParameter(CategoriesInfo.Field.ID.ToString(), ID);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(CategoriesInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            connect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public bool IsConstanePro(int ID)
        {
            string sErr = "";
            string query = "Select top 1 [ID] From  tbl_Products  Where IDCategory=" + ID;
            connect();
            int id = -1;
            try
            {
                id = Convert.ToInt32(executeScalar(query));
            }
            catch (Exception ex)
            {
                id = -1;
                sErr = ex.Message;
                return false;
            }
            if (id == 0)
                return false;
            //      if (sErr != "") ErrorLog.SetLog(sErr);
            return true;
        }
        #endregion Method
        public DataTable GetLeafsCategories(ref string sErr)
        {
            connect();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = _strSPLeafs;

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetRootsCategories(String Type, ref string sErr)
        {
            connect();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;

            if (Type == "")
                command.CommandText = _strSPRoots;
            else

                command.CommandText = _strSPRootsType;


            AddParameter("Type", Type);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;

            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetCategoriesChilds(int LinkedID, ref string sErr)
        {
            connect();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = _strSPGetChilds;

            AddParameter("LinkedID", LinkedID);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable get_child(Int64 ID, ref string sErr)
        {
            string query = "exec " + _strSPGetChilds + " " + ID.ToString();
            DataTable list = new DataTable();
            connect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            return list;
        }

        public DataTable GetAllChilds(int cate, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllChilds);

            AddParameter("ID", cate);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public string GetCateByPro(Int64 idpro, ref string sErr)
        {
            string result = "";
            connect();
            InitSPCommand(_strSPGetIDByPro);
            AddParameter("ProductID", idpro);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            foreach (DataRow row in list.Rows)
            {
                result += "," + row["IDCode"].ToString();
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            if (result.Length > 0)
                result = result.Substring(1);
            return result;
        }

        public DataTable GetCateAllByPro(Int64 idpro, string notType, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPCateAllByPro);
            AddParameter("ProductID", idpro);
            AddParameter("NotType", notType);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
           
            disconnect();

            return list;
        }

        public DataTable GetAllParents(int id, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllParents);

            AddParameter("ID", id);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
    }
}
